﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using AspNetCs.Config;
using System.Web;
using System.Data;
using System.IO;

namespace AspNetCs.SQLServerDAL
{
    public class db : IDisposable
    {
        private SqlConnection _Connection;//定义_Connection为SqlConnection对象,
        private static string ConnStr
        {
            get
            {
                return BaseConfig.GetBaseConfig().DbConnectstring;

            }
        }

        public db()
        {
            this._Connection = GetConnStr();
        }

        private SqlConnection GetConnStr()
        {
            this._Connection = new SqlConnection(ConnStr);
            return this._Connection;
        }
        /// <summary>
        /// 定义一个结构体(设置参数的基本属性)
        /// </summary>
        public struct ParameterArry
        {
            /// <summary>
            /// 参数名称
            /// </summary>
            public string parName;
            /// <summary>
            /// 参数类型
            /// </summary>
            public SqlDbType dbType;
            /// <summary>
            /// 参数大小
            /// </summary>
            public int size;
            /// <summary>
            /// 参数传入的变量值
            /// </summary>
            public object objValue;
            /// <summary>
            /// 表示此参数是否为输出
            /// </summary>
            public ParameterDirection PbDirection;

        }
        #region SQLCommand

        /// <summary>
        /// 返回SQLCommand对象
        /// </summary>
        /// <param name="storedProcNameOrSqlString">存储过程名称或者SQL</param>
        /// <param name="parameter">参数数组</param>
        /// <param name="types">Command类型</param>
        /// <returns></returns>
        private SqlCommand DataCommand(string storedProcNameOrSqlString, ParameterArry[] parameter, CommandType types)
        {
            SqlCommand Command = new SqlCommand(storedProcNameOrSqlString, this._Connection);
            Command.CommandType = types;
            for (int i = 0; i < parameter.Length; i++)
            {
                ParameterArry strpar = parameter[i];
                SqlParameter sp = new SqlParameter(strpar.parName, strpar.dbType, strpar.size);
                sp.Direction = strpar.PbDirection;
                sp.Value = strpar.objValue;
                Command.Parameters.Add(sp);
            }
            return Command;

        }

        /// <summary>
        /// 返回存储过程的SQLCommand对象,该方法只能操作存储过程
        /// </summary>
        /// <param name="storedProcNameOrSqlString">存储过程名称</param>
        /// <param name="Parameter">参数组</param>
        /// <returns></returns>
        private SqlCommand DataCommand(string storedProcNameOrSqlString, ParameterArry[] Parameter)
        {
            return this.DataCommand(storedProcNameOrSqlString, Parameter, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 返回SQL语句的SQLCommand对象,该方法只能执行SQl语句
        /// </summary>
        /// <param name="storedProcNameOrSqlString">SQL语句</param>
        /// <param name="Parameter"></param>
        /// <returns></returns>
        private SqlCommand DataTextCommand(string storedProcNameOrSqlString, ParameterArry[] Parameter)
        {
            return this.DataCommand(storedProcNameOrSqlString, Parameter, CommandType.Text);
        }

        /// <summary>
        /// 返回存储过程的ReturnVale值
        /// </summary>
        /// <param name="storedProcNameOrSqlString">存储过程名称</param>
        /// <param name="Parameter">参数组</param>
        /// <returns></returns>
        private SqlCommand DataIntCommand(string storedProcNameOrSqlString, ParameterArry[] Parameter)
        {
            SqlCommand Command = this.DataCommand(storedProcNameOrSqlString, Parameter);
            Command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, true, 0, 0, string.Empty, DataRowVersion.Default, null));
            return Command;
        }

        #endregion
        #region 直接执行SQL语句


        /// <summary>
        /// 执行SQL，返回DataReader
        /// </summary>
        /// <param name="StrSql">SQL的语句</param>
        /// <returns></returns>
        public SqlDataReader GetSqlDataReader(string StrSql)
        {
            db.ParameterArry[] Par = new ParameterArry[0];
            this._Connection.Open();
            SqlCommand Command = this.DataTextCommand(StrSql, Par);
            return Command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>
        /// 执行SQL,返回DataRedaer
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public SqlDataReader GetSqlDataReader(string Sqlstr, ParameterArry[] par)
        {
            this._Connection.Open();
            SqlCommand Command = this.DataTextCommand(Sqlstr, par);
            return Command.ExecuteReader(CommandBehavior.CloseConnection);

        }


        /// <summary>
        /// 执行SQL,无返回
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        public void Execute_Noget(string SqlString)
        {
            try
            {
                this._Connection.Open();
                db.ParameterArry[] par = new ParameterArry[0];
                SqlCommand Command = this.DataTextCommand(SqlString, par);
                Command.ExecuteNonQuery();
            }
            finally
            {
                this._Connection.Close();
                close();
            }

        }

        /// <summary>
        /// 执行SQL语句,返回受影响的行数
        /// </summary>
        /// <param name="Sql">SQL语句子</param>
        /// <returns>返回受影响的行数</returns>
        public int ExcSql(string Sql)
        {
            int IsOk = 0;
            try
            {
                this._Connection.Open();
                SqlCommand Command = new SqlCommand(Sql, this._Connection);
                Command.CommandType = CommandType.Text;
                IsOk = Command.ExecuteNonQuery();
            }
            finally
            {
                this._Connection.Close();
            }
            return IsOk;
        }

        public int ExcSql(string Sql, ParameterArry[] Parameter)
        {
            int Result = 0;
            try
            {
                this._Connection.Open();
                SqlCommand Command = this.DataTextCommand(Sql, Parameter);
                Result = Command.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                Result = -1;
                Log(ex);
            }
            return Result;
        }


        /// <summary>
        /// 执行SQL返回首行首列
        /// </summary>
        /// <param name="Strsql"></param>
        /// <returns></returns>
        public object GetOneFile(string Strsql,ParameterArry[] par)
        {
            object obj = null;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = this.DataTextCommand(Strsql, par);
            DataSet ds = new DataSet();
            try
            {
                this._Connection.Open();
                adapter.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    obj = ds.Tables[0].Rows[0][0];
                }
            }
            finally
            {
                this._Connection.Close();

            }
            return obj;


        }




        /// <summary>
        /// 执行SQL返回首行首列
        /// </summary>
        /// <param name="Strsql"></param>
        /// <returns></returns>
        public object GetOneFile(string Strsql)
        {
            object obj = null;
            SqlDataAdapter adapter = new SqlDataAdapter(Strsql, this._Connection);
            DataSet ds = new DataSet();
            try
            {
                this._Connection.Open();
                adapter.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    obj = ds.Tables[0].Rows[0][0];
                }
            }
            finally
            {
                this._Connection.Close();

            }
            return obj;


        }






        /// <summary>
        /// 执行SQL返回DATASET
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public DataSet GetSql_DataSet(string Sqlstr)
        {
            try
            {
                DataSet ds = new DataSet();
                this._Connection.Open();
                db.ParameterArry[] par = new ParameterArry[0];
                SqlDataAdapter adp = new SqlDataAdapter();
                adp.SelectCommand = this.DataTextCommand(Sqlstr, par);
                adp.Fill(ds);
                return ds;

            }
            catch (SqlException q)
            {
                throw q;
            }
            finally
            {
                this._Connection.Close();
                close();
            }
        }
        /// <summary>
        /// 执行SQL，返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetSql_DataTable(string sql)
        {
            this._Connection.Open();
            DataTable dt = new DataTable();
            try
            {
                db.ParameterArry[] par = new ParameterArry[0];
                SqlDataAdapter adp = new SqlDataAdapter();
                adp.SelectCommand = this.DataTextCommand(sql, par);
                adp.Fill(dt);
            }
            finally
            {
                this._Connection.Close();
            }
            return dt;
        }

        /// <summary>
        /// 执行SQL，返回DataTable
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="par">db.ParameterArry结构体</param>
        /// <returns></returns>
        public DataTable GetSql_DataTable(string sql, ParameterArry[] par)
        {
            this._Connection.Open();
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter adp = new SqlDataAdapter();
                adp.SelectCommand = this.DataTextCommand(sql, par);
                adp.Fill(dt);
            }
            finally
            {
                this._Connection.Close();
            }
            return dt;
        }


        /// <summary>
        /// 执行SQL，返回DataTable
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="Sp">System.Data.SqlClient.SqlParameter对象</param>
        /// <returns></returns>
        public DataTable GetSql_DataTable(string sql, SqlParameter[] Sp)
        {
            this._Connection.Open();
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter adp = new SqlDataAdapter();
                SqlCommand Commend = new SqlCommand(sql, this._Connection);
                Commend.Parameters.AddRange(Sp);
                adp.SelectCommand = Commend; 
                adp.Fill(dt);
            }
            finally
            {
                this._Connection.Close();
            }
            return dt;
        }


        /// <summary>
        ///返回DATASET
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pagesize">分页数</param>
        /// <param name="recordcount">out 返回总数[int]</param>
        /// <returns></returns>
        public DataSet GetPage(string sql, int currentPage, int pagesize, out int recordcount)
        {
            recordcount = GetPageRecord(sql);
            if (currentPage > recordcount)
            {

                currentPage = recordcount;

            }
            if (currentPage <= 0)
            {

                currentPage = 1;
            }

            DataSet ds = new DataSet();
            int startRow = (currentPage - 1) * pagesize;



            SqlDataAdapter sad = new SqlDataAdapter(sql, this._Connection);
            sad.Fill(ds, startRow, pagesize, "PagerTable");
            return ds;
        }
        public int GetPageRecord(string sql)
        {
            sql = System.Text.RegularExpressions.Regex.Replace(sql.ToLower(), "order by.*", "");
            sql = "select count(*) from (" + sql + ") as temp";
            SqlCommand cmd = new SqlCommand(sql, this._Connection);
            cmd.Connection.Open();
            int recordcount = (int)cmd.ExecuteScalar();
            return recordcount;
        }




        #endregion


        #region 执行存储过程

        /// <summary>
        /// 执行存储过程 返回DataTable
        /// </summary>
        /// <param name="StrParameter">存储过程名称</param>
        /// <param name="Parameter">参数</param>
        /// <param name="TableName">表名</param>
        /// <returns></returns>
        public DataTable RunDataSetParameter(string StrParameter, ParameterArry[] Parameter, string TableName)
        {
            DataTable dt = new DataTable(TableName);
            try
            {
                this._Connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = this.DataCommand(StrParameter, Parameter);
                adapter.Fill(dt);
            }
            finally
            {
                this._Connection.Close();
            }
            return dt;
        }

        #endregion







        #region 释放Connecction对象

        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        public void close()
        {
            this._Connection.Close();
            this._Connection.Dispose();
        }
        public void Dispose()
        {
            this.dispose(true);
            GC.SuppressFinalize(this);
        }
        /// <summary>
        /// 释放所有资源
        /// </summary>
        /// <param name="disposeing"></param>
        protected virtual void dispose(bool disposeing)
        {
            if (disposeing && (this._Connection != null))
            {
                this._Connection.Dispose();
            }
        }

        /// <summary>
        /// 析构函数
        /// </summary>
        ~db()
        {
            this.dispose(false);
        }
        #endregion


        /// <summary>
        /// 把异常写入日志
        /// </summary>
        /// <param name="ex"></param>
        private void Log(Exception ex)
        {
            //string path = HttpContext.Current.Request.PhysicalApplicationPath + "ex/ExceptionLog.txt";
            string path ="E:\\ex\\ExceptionLog.txt";
            //if (!File.Exists(path))
            //{
            //    File.Create(path);
            //}
            StringBuilder builder = new StringBuilder();
            builder.Append(HttpContext.Current.Request.RawUrl + "       " + DateTime.Now.ToString());
            builder.Append("\r\n");
            builder.Append("\r\n");
            builder.Append(ex.Source);
            builder.Append("\r\n");
            builder.Append("\r\n");
            builder.Append(ex.Message);
            builder.Append("\r\n");
            try
            {
                StreamWriter w = File.AppendText(path);
                Log(builder.ToString(), w);
                w.Close();
            }
            catch (UnauthorizedAccessException)
            {
            }
        }

        private void Log(string logMessage, TextWriter w)
        {
            w.Write("\r\n\r\n");
            w.WriteLine("{0}", logMessage);
            w.WriteLine("-------------------------------");
            w.Flush();
        }
    }
}
